<?php

/* 
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

//-------------------Database Class--------------//

define('MSB_VERSION', '1.0.0');

define('MSB_NL', "\r\n");

define('MSB_STRING', 0);
define('MSB_DOWNLOAD', 1);
define('MSB_SAVE', 2);
define('__SEP__', "/*sep*/" );

set_time_limit( 600 );

class Database {
       
       var $file = '';
       var $server = '';
       var $port = '';
       var $username = '';
       var $password = '';
       var $database = '';
       var $link_id = -1;
       var $connected = false;
       var $tables = array();
       var $drop_tables = true;
       var $struct_only = false;
       var $comments = true;
      // var $backup_dir = dirname(__FILE__). '/../backup/';
       var $fname_format = 'd_m_y__H_i_s';
       var $complete_inserts  = false;
       var $inserts_block     = 200;
	   
       
       function __construct(){
    
           global $conf;
           
           $this-> server = $conf['DB_CON_PARAM']['HOST'];
           $this-> port = $conf['DB_CON_PARAM']['PORT'];
           $this-> username = $conf['DB_CON_PARAM']['USERNAME'];
           $this-> password = $conf['DB_CON_PARAM']['PASSWORD'];
           $this-> database = $conf['DB_CON_PARAM']['DATABASE'];
                              
       }
   
	   
/*----NOT USED YET-------------------------*/
	function getFilePath($file_id) {
        
        $name = $_FILES[$file_id]["name"];
        $type = $_FILES[$file_id]["type"];
        $size = $_FILES[$file_id]["size"];
        $tmp_name = $_FILES[$file_id]["tmp_name"];
        $error = $_FILES[$file_id]["error"];
		
		//$_SESSION['account_no']=$_POST['account_no'];

        if($error > 0){
            $_SESSION['failure_notification'] = "Error occured in choosing the Destination file! Error code: ".$error;
            return "";
        }
        //elseif (($type != "image/jpg") && ($type != "image/jpeg") && ($type != "image/JPG") && ($type != "image/JPEG") && ($type != "image/png") && ($type != "image/tiff") && ($type != "image/bmp") && ($type != "image/gif")) {
            //$_SESSION['failure_notification'] = "This Picture Format '{$type}' is not allowed!";
            //return "";
        //}
        //elseif ($size > 10000000) {
            //$_SESSION['failure_notification'] = "Pictures with size greater than 10MB are not allowed!";
            //return "";
        //}
        else{
            //move_uploaded_file($tmp_name, $folder."/".$name);
			//$_SESSION['failure_notification'] = "Pictures was uploaded successfully!";
			$path = realpath($name);
            return $path;
        }
        
   }

   function backup($task = MSB_SAVE, $fname = '', $compress = false) {
	   //
       if ( !( $sql = $this->retrieve() ) ) {
           return false;
       }
       if ( $task == MSB_SAVE ) {
           if (empty($fname)) {
			   //if($_FILES["backup_destination"]["name"] != ""){
			   		//$fname = $this->GetFilePath("backup_destination");
			   //}else{
			   		$fname = dirname(__FILE__).'/../backup/';
					$fname .= date($this->fname_format);
               		$fname .= ($compress ? '.sql.gz' : '.sql');
			   //}
               
           }
           return $this->saveToFile($fname, $sql, $compress);
		   //$_SESSION['success_notification'] = "The Database Backup was created successfully!";
       } elseif ($task == MSB_DOWNLOAD) {
           if ( empty( $fname ) ) {
               $fname = date($this->fname_format);
               $fname .= ($compress ? '.sql.gz' : '.sql');
           }
           return $this->downloadFile($fname, $sql, $compress);
		   //$_SESSION['success_notification'] = "The Database Backup was created successfully!";
       } else {
           return $sql;
       }
   }

   function connect() {
       $value = false;
       if (!$this->connected) {
           
           $host = trim($this->server).':'.trim($this->port);
           $this->link_id = mysql_connect($host, trim($this->username), trim($this->password));
           
       }
       if ($this->link_id) {
           if (empty($this->database)) {
               $value = true;
           } elseif ($this->link_id !== -1) {
               $value = mysql_select_db(trim($this->database), $this->link_id);
           } else {
              $value = mysql_select_db(trim($this->database));
           }
       }
       if (!$value) {
           throw new Exception(mysql_error());
       }
       return $value;
   }


   function query($sql) {
       if ($this->link_id !== -1) {
           $result = mysql_query($sql, $this->link_id);
       } else {
           $result = mysql_query($sql);
       }
       if (!$result) {
            throw new Exception(mysql_error());
       }
       return $result;
   }


   function getTables() {
       $value = array();
       if ( !( $result = $this->query('SHOW TABLES') ) ) {
           return false;
       }
       while ( $row = mysql_fetch_row( $result ) ) {
           if ( empty( $this->tables) || in_array( $row[0], $this->tables ) ) {
               $value[] = $row[0];
           }
       }
       if (!sizeof($value)) {
            throw new Exception('No tables found in database.'); ;
           return false;
       }
       return $value;
   }


   function dumpTable( $table ) {
       $value = '';
       $this->query( 'LOCK TABLES ' . $table . ' WRITE' );
       if ( $this->comments ) {
           $value .= '#' . MSB_NL;
                   $value .= '# Table structure for table `' . $table . '`' . MSB_NL;
                   $value .= '#' . MSB_NL . MSB_NL;
       }
       if ( $this->drop_tables ) {
           $value .= 'DROP TABLE IF EXISTS `' . $table . '`;' . __SEP__ . MSB_NL;
       }
       if ( !( $result = $this->query('SHOW CREATE TABLE ' . $table) ) ) {
           return false;
       }
       $row = mysql_fetch_assoc($result);
       $value .= str_replace("\n", MSB_NL, $row['Create Table']) . ';' . __SEP__;
       $value .= MSB_NL . MSB_NL;
       if (!$this->struct_only) {
               if ($this->comments) {
                   $value .= '#' . MSB_NL;
                   $value .= '# Dumping data for table `' . $table . '`' . MSB_NL;
                   $value .= '#' . MSB_NL . MSB_NL;
               }
               $value .= $this->getInserts($table);
       }
       $value .= MSB_NL . MSB_NL;
       $this->query('UNLOCK TABLES');
       return $value;
   }

   function getInserts($table) {
       $value = '';
       if (!($result = $this->query('SELECT * FROM ' . $table))) {
           return false;
       }
       if ( $this->complete_inserts ) {
               while ($row = mysql_fetch_row($result)) {
                   $values = '';
                   foreach ($row as $data) {
                       $values .= '\'' . addslashes($data) . '\', ';
                   }
                   $values = substr($values, 0, -2);
                   $value .= 'INSERT INTO ' . $table . ' VALUES (' . $values . ');' . __SEP__ . MSB_NL;
               }
       } else {
               $blocks_counter = 0;
           $blocks = array();
           while ($row = mysql_fetch_row($result)) {
               $values = array();
               foreach ($row as $data) {
                   $values[] = '\'' . addslashes($data) . '\'';
               }
               $blocks[] = '(' . implode( ',', $values ) . ')';

               if ( $blocks_counter < $this->inserts_block ) {
                   $blocks_counter++;
               } else {
                       $value .= 'INSERT INTO ' . $table . ' VALUES ' . implode( ',', $blocks ) . ";" . __SEP__ . MSB_NL;
                       $blocks = array();
                       $blocks_counter = 0;
               }
           }
               if ( count( $blocks ) ) {
               $value .= 'INSERT INTO ' . $table . ' VALUES ' . implode( ',', $blocks ) . ";" . __SEP__ . MSB_NL;
               }
       }
       return $value;
   }


   function retrieve() {
       $value = '';
       if (!$this->connect()) {
           return false;
       }
       if ($this->comments) {
           $value .= '#' . MSB_NL;
           $value .= '# MySQL database dump' . MSB_NL;
           $value .= '# Created by MySQL_Backup class, ver. ' . MSB_VERSION . MSB_NL;
           $value .= '#' . MSB_NL;
           $value .= '# Host: ' . $this->server . MSB_NL;
           $value .= '# Generated: ' . date('M j, Y') . ' at ' . date('H:i') . MSB_NL;
           $value .= '# MySQL version: ' . mysql_get_server_info() . MSB_NL;
           $value .= '# PHP version: ' . phpversion() . MSB_NL;
           if (!empty($this->database)) {
               $value .= '#' . MSB_NL;
               $value .= '# Database: `' . $this->database . '`' . MSB_NL;
           }
           $value .= '#' . MSB_NL . MSB_NL . MSB_NL;
       }
       if (!($tables = $this->getTables())) {
           return false;
       }
       foreach ($tables as $table) {
           if (!($table_dump = $this->dumpTable($table))) {
                throw new Exception(mysql_error());
               return false;
           }
           $value .= $table_dump;
       }
       return $value;
   }


   function saveToFile($fname, $sql, $compress) {
       if ($compress) {
           if (!($zf = gzopen($fname, 'w9'))) {
              throw new Exception('Can\'t create the output file1.');;
	      return false;
           }
           gzwrite($zf, $sql);
           gzclose($zf);
       } else {
               if (!($f = fopen($fname, 'w'))) {
              throw new Exception('Can\'t create the output file1.');
                   return false;
               }
               fwrite($f, $sql);
               fclose($f);
       }
	return true;
   }

   function downloadFile($fname, $sql, $compress) {
       header('Content-disposition: filename=' . $fname);
       header('Content-type: application/octetstream');
       header('Pragma: no-cache');
       header('Expires: 0');
       echo ($compress ? gzencode($sql) : $sql);
	   $_SESSION['success_notification'] = "The Database Backup was created successfully!";
       return true;
   }
   
   
function get($table, $fields , $where = array(), $limit = NULL, $order_by = array()) {

    if ($table == null)
        throw new Exception("table param invalid");
    if (!is_array($fields) && $fields !== null)
        throw new Exception("fields param invalid");
    if (!is_array($where) || $where == null)
        throw new Exception("where param invalid");

    try {
        $database = new Database();
        $database->connect();
    } catch (Exception $exc) {
        throw new Exception("could not connect to database");
    }

     //if fields is set sanitize 
    foreach ($fields as $key => $value){
        if(is_string($value)) $fd = split(':', $value);
        $rd['fields'][$key] = "{$this->quote_fields($table)}.{$this->quote_fields($fd[0])} {$fd[1]}";        
    }
    
     //extract custom fields
    if(is_array($fields[-1]))
        foreach ($fields[-1] as $key => $value){
            $rd['fields'][] = "($value) as $key";        
        }
    unset($rd['fields'][-1]);
    
    //if where is set sanitize 
    foreach ($where as $key => $value){
        if(empty($value)) continue;
        $rd['where'][$key] = "$key in(".$this->quote_values($value).")"; 
    }
    

    if ($fields) $query = "SELECT ".implode(',', $rd['fields'])." from $table ";
    else $query = "SELECT * from $table ";
        
    if (isset($where)) $query = $query. "where " . implode(' and ', $rd['where']);
    if (isset($limit)) $query = $query. "limit ". implode(',', $limit);
    if (isset($order_by)) $query = $query. "order by ". implode(',',$order_by);
    
    try {
        $result = $database->query($query);

        while ($row = mysql_fetch_assoc($result)) {
            $rc[] = $row;
        }
    } catch (Exception $exc) {
        throw new Exception('fatal error in get ::: ' . $exc);
    }

    return $rc;
}


  function quote_fields($name) {

    /**

    Similar to quote_param(), but quote one or more SQL names instead (table
    name, field name, etc.).

    **/

    // the following code is SQL standard compliant; do not touch it
    //
    // also note that preg_match() here is used to preserve, unquoted, subscripts used
    // with array fields

    if (is_array($name)) {

      // array: quote each name and return them as a comma-separated list (do
      // not remove the double quotes below; they make sure that the name being
      // quoted is a string)

      $s = '';
      foreach ($name as $p => $q) {
        if (!empty($s)) $s .= ',';
        if (!is_int($p)) $s .= '"' . str_replace('"','""',"$p") . '" as ';
        if (preg_match('/^(.*)(\[[0-9]+\])$/',"$q",$m)) {
          $q = $m[1];
          $subscript = $m[2];
        } else {
          $subscript = '';
        }
        $s .= '`' . str_replace('"','""',$q) . '`' . $subscript;
      }

      return $s;

    } else {

      // anything else: quote the name and return it as a string (do not remove
      // the double quotes below; they make sure that the name being quoted is
      // a string)

      if (preg_match('/^(.*)(\[[0-9]+\])$/',"$name",$m)) {
        $name = $m[1];
        $subscript = $m[2];
      } else {
        $subscript = '';
      }

      return '`' . str_replace('"','""',$name) . '`' . $subscript;

    }

  }

   function quote_values($value,$null = 'null') {

    /**

    This function helps avoid SQL code injection by escaping/quoting a
    user-supplied parameter (also works with arrays representing comma-separated
    lists).

    **/

    if ($value === null) return $null;

    // the following code is SQL standard compliant; do not touch it

    if (is_array($value)) {

      // array: quote each value and return them as a comma-separated list (do
      // not remove the double quotes below; they make sure that the value being
      // quoted is a string)

      if (empty($value)) return $null;

      foreach ($value as &$p) {
        $p = "$p";
        $p = ($p === '' ? $null : ("'" . str_replace("'","''",$p) . "'"));
      }

      return implode(',',$value);

    } else {

      // anything else: quote the value and return it as a string (do not remove
      // the double quotes below; they make sure that the value being quoted is
      // a string)

      $value = "$value";
      return $value === '' ? $null : ("'" . str_replace("'","''",$value) . "'");

    }

  }



function save($table, $id = NULL, $values) {

    if ($table == null)
        throw new Exception("table param invalid");
    if (!is_array($values) || $values == null)
        throw new Exception("value param invalid");
    if (!is_int((int)$id) || ($id < 0))
        throw new Exception("id param invalid");
    
    foreach ($values as $key => $val) {
        if (!is_string($val)) $values[$key] = "$val";
    }

    try {
        $database = new Database();
        $database->connect();
    } catch (Exception $exc) {
        throw new Exception("could not connect to database");
    }

    $query = "Show columns from " . $table;
    $col = $database->query($query);
    
    if ($id === NULL) {
        while ($row = mysql_fetch_assoc($col)) {
            if(array_key_exists($row['Field'], $values)){
                $record[$row['Field']] = "'{$values[$row['Field']]}'";
                unset($values[$row['Field']]);
            }
        } 
        if(!empty($values)) throw new Exception("fields (".implode(',', array_keys($values)).") do not exist in the database");
        
        $order = '(' . implode(',', array_keys($record)) . ')';
        
        $query = "Insert into $table $order values(" . implode(',', $record) . ")";
    } else {
        
        $query = "Show keys from $table where key_name = 'primary'";
        try {
            $pri = mysql_fetch_assoc($database->query($query));
        } catch (Exception $ex) {
            throw new Exception('primary key not set on table '.$table);
        }
        try {

            while ($row = mysql_fetch_assoc($col)) {
                if(array_key_exists($row['Field'], $values)){
                    $record[$row['Field']] = "{$row['Field']} = '{$values[$row['Field']]}'";
                    unset($values[$row['Field']]);
                }
            } 
            if(!empty($values)) throw new Exception("fields (".implode(',', array_keys($values)).") do not exist in the database");
        
            
        } catch (Exception $exc) {
            throw new Exception('error trying to prepare update ::: ' . $exc);
        }

            $query = "Update $table set " . implode(',', $record) . " where " . $pri['Column_name'] . " = " . $id;
    }

    try {
        $database->query($query);
        $id = $id ? $id : mysql_insert_id();
        if($id) return $id;
        else throw new Exception('Database Error: could not save record');
    } catch (Exception $exc) {
        throw new Exception('Fatal error in save ::: ' . $exc);
    }
   return FALSE;
}

function customQ($q) {

    try {
        $database = new Database();
        $database->connect();
    } catch (Exception $exc) {
        throw new Exception("could not connect to database");
    }


    try {
        $result = $database->query($q);
    } catch (Exception $exc) {
        throw new Exception('fatal error in get ::: ' . $exc);
    }
      if($result == 1) return 'Successfully Completed Task';
    return $result;
}

}
